package com.neca.auction.dbb;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.List;



import org.hibernate.Query;
import org.hibernate.Session;

import com.neca.auction.model.Offer;
import com.neca.auction.model.Product;
import com.neca.auction.model.User;
import com.neca.auction.util.HibernateUtility;

public class DBBroker {
	
	public static DBBroker dbbroker;
	Session session;

	private  DBBroker() {
		
		this.session = HibernateUtility.getSessionFactory().openSession();
		session.beginTransaction();
		
				
		session.createSQLQuery("CREATE TABLE user (userID INTEGER NOT NULL IDENTITY,firstName VARCHAR(50),lastName VARCHAR(50),userName VARCHAR(50),password VARCHAR(50),phone VARCHAR(50),mail VARCHAR(50),status INTEGER,PRIMARY KEY (userID))\n"+
				"CREATE TABLE product (productID INTEGER NOT NULL IDENTITY,name VARCHAR(255),category INTEGER,description VARCHAR(8000),price DOUBLE,userID INTEGER,PRIMARY KEY (productID))\n"+
				"CREATE TABLE offer (offerID INTEGER NOT NULL IDENTITY,productID INTEGER,userID INTEGER,price DOUBLE,PRIMARY KEY (offerID))\n"+
				"INSERT INTO USER VALUES(0,'Negovan','Djurovic','necadju','neca1706','0643140225','necadju@gmail.com',1)\n"+
				"INSERT INTO USER VALUES(1,'Vladimir','Djurovic','vladadju','nc86vl87','22222222222','vladadju@gmail.com',1)\n"+
				"INSERT INTO USER VALUES(2,'Borivoje','Djurovic','boradju','bora','354354353','boradju@gmail.com',1)\n"+
				"INSERT INTO USER VALUES(3,'Sezana','Djurovic','sneza','sneza','sdfdsffsd','sneza@yahoo.com',1)\n"+
				"INSERT INTO PRODUCT VALUES(1,'OPEL ASCONA 1.8i',2,'1985-to, 74 kw, atestiran TNG, u voznom stanju, pumpa za benzin ne radi, sajtne i rubovi su losi, gume kao nove, odjavljen...',60000.0E0,0)\n"+
				"INSERT INTO PRODUCT VALUES(2,'SPORET POVOLJNO!!!111',0,'Sporet je marke Iskra i u tehnicki ispravnom stanju (jedino prekidac\u00c2\u008d za vremenski brojac\u00c2\u008d za rernu nije ispravan). Sve cetiri ringle i rerna rade normalno i jos uvek je u upotrebi. Prodaje se iz razloga zamene za drugi ugradni sporet. Organizaciju i troskove transporta snosi kupac. Srecna licitacija!',4445.0E0,0)\n"+
				"INSERT INTO PRODUCT VALUES(3,'drvene police',3,'ksjdhfkjsdhfkjdsfh skjfhsdkjfhsdjkfhs ksjfhsfkjhs',990.0E0,0)\n"+
				"INSERT INTO PRODUCT VALUES(4,'Sako - BOSS',1,'Izuzetno ocuvan sako nosen svega par puta, marke Hugo Boss, velicine 52. Pogledajte moje ostale aukcije: http://www.limundo.com/Clan/bokcan/SpisakAukcija',377.0E0,0)\n"+
				"INSERT INTO PRODUCT VALUES(5,'laptop IBM pentijum 4 u extra stanju',0,'Ibm T 40 pentijum 4 1500 mhz 512 ddr  40 gb hdd  dvd  cdrw  wifi  win xp licenca  x usb 2.0 mrezna , modem osvetljenje tastature baterija dobra drzi preko sat adapter laptop je jako lepo ocuvan kao da je sad iz prodavnice',10001.0E0,0)\n"+
				"INSERT INTO PRODUCT VALUES(6,'Zagor, ZS broj 465',3,'Spolja levi donji ugao malo ostecen, zadnja korica iznutra malo prljava, druga polovina stripa odvojena sto se vidi na slici.',326.0E0,0)\n"+
				"INSERT INTO PRODUCT VALUES(7,'Ford Siera',2,'Ford siera u super stanju. Motor 2.0 clx 1998 cm3,1989 godiste.88kw Limarijski kompletno sredjen i zasticen,komplet ofarban pre tri meseca. Stavljen nov auspuh pod garancijom. Akumulator nov,pod garancijom. Stavljen je novi hladanjak i ventilator ''visko''. Nove zadnje gume,zimske pod garancijom. Od opreme ima: alarm, centralnu, siber, podizace-koji ne rade ne znam zasto zavesice na zadnjim staklima, plin koji je potrebno atestirati. Kvacilo mu je tvrdje jedino to nisam sredio,ali normalno ulazi u brzine. Extra auto za male pare. Auto nije registrofan,a registracija je oko 27.000 i plus ove nove tablice. ',79990.0E0,1)\n"+
				"INSERT INTO PRODUCT VALUES(9,'GPS Navigacija GALEB group',0,'Odlicna navigacija GALEB group, uz njega dajem postolje za auto i kablic za punjenje preko USB-a, navigacija ima olovcicu sto mozete videti, ubacene su mape istocne evrope i naravno Srbije. Ima jednu manu a to je taster za paljenje koji je izgubio kontakt verovatno nisam ga nosio na servis da vidim sta je, ali se pali na drugi prekidac bez problema. Eto to je to za sva ostala pitanja pisi te, srecna licitacija...',2000.0E0,1)\n"+
				"INSERT INTO PRODUCT VALUES(10,'GIORGIO ARMANI KOSULJA 10 DIN!!!',1,'NOVA GIORGIO ARMANI KOSULJA....(obukao sam je za potrebe aukcije) VELICINA L ...(na njoj pise 16 1\2--neke njihove mere) UKOLIKO AUKCIJA PREDJE 1000 DIN GRATIS dobijate PIERRE MONEE kosulju (nosena na 2-3 zurke) ',10.0E0,1)\n"+
				"INSERT INTO PRODUCT VALUES(11,'FORD ZA SAMO 15000 DINARA',2,'FORD ESKORT JE U VOZNOM STANJU ,ALI JE PATOS DOSTA TRUO ,MOTOR TROSI ULJE I NIJE ZA REGISTRACIJU. REGISTROVAN JE BIO DO 15.11.2011. STAVLJAM GA NA AUKCIJU ZA REZERVNE DELOVE. IMA PLIN SA PAPIRIMA, SPOJLERE, REP I JOS DOSTA DELOVA , ZNACI KOMPLETAMN AUTO SAMO BEZ AKUMULATORA. KOGA ZANIMA MOZEMO DA GA UPALIMO I ON JE U VOZNOM STANJU, ALI GA PRODAJEM SAMO KAO ZA REZERVNE DELOVE A AKO NEKO HOCE DA GA VOZI VASA ODGOVORNOST. SRECNA LICITACIJA',15000.0E0,1)\n"+
				"INSERT INTO PRODUCT VALUES(12,'WEB KAMERA CRETIVE',0,'KORISTENA DO PRE 2 GOD. KADA JE ZAMENJENA BOLJOM, OD TADA JE NEKORISTENA. NISAM JE TESTIRAO JER JE CD SA DRAJVERIMA ZAGUBLJEN ZATO IDE KAO NEISPRAVNA (KO IMA VREMENA I ZIVACA NEKA TRAZI PO NETU). IDE U STANJU KAO NA SLIKAMA. KOME TREBA NEKA LICITIRA.',151.0E0,2)\n"+
				"INSERT INTO PRODUCT VALUES(13,'Sat Ferari (Quartz watch) Beli',3,'Sat Ferari (Quartz watch) Beli XXL Nov savremen sat za sve koji zele vise od zivota. Sat je nov, (nekoriscen) iz nove kolekcije FERARI White, Kuciste od nerdjajuceg celika. ',501.0E0,2)\n"+
				"INSERT INTO PRODUCT VALUES(14,'gliser sa motorom',2,'-gliser italijansko korito 4,5m 300kg, sa prikolicom -soferka polomljena moze da se popravi -imapa papire nekad bio registrovan -motor jamaha 40 konja bez alnasera -ispravno moze da se vozi i vuce skijasa -prodaja moze iodvojeno -motor 1000 eur -camac sa prikolcom i komandama 1200 eur',220000.0E0,2)\n"+
				"INSERT INTO PRODUCT VALUES(15,'NOVE Vunene Carape **Rucni Rad** Vel. 42-44',1,'Nove jako tople vunene carape-rucni rad, odgovaraju broju 42-44. Postanske troskove placa kupac. ',99.0E0,2)\n"+
				"INSERT INTO PRODUCT VALUES(16,'Zipoo upaljac ( kupljen 1985)',3,'Zipoo upaljac star 26 godina, kupljen davne 1985 godine, ispravan radi mislim da bi mu trebalo zameniti fitilj ali i sad radi.',1999.0E0,2)\n"+
				"INSERT INTO PRODUCT VALUES(17,'NOVE NAOCARE ZANZARA',3,'ODLICNE NOVE ZANZARA ITALIJANSKE NAOCARE ,CRNA BOJA ,TRENUTNO SU PROBNA STAKLA NA NJIMA ,KOD OPTICARA ZAMENJIVA U DIOPTRIJSKA....',499.0E0,3)\n"+
				"INSERT INTO PRODUCT VALUES(18,'APPLE iPad2 tablet Wi-Fi 16GB - NOV!!!',0,'Apple iPad2 tablet Wi-Fi 16GB, potpuno nov,sa garancijom.Najnoviji Apple uredjaj,vrhunskih karakteristika,vrlo praktican,po jako povoljnoj ceni... Ne treba trositi reci,ko je radio,ili radi na Apple-u zna o cemu govorim. Specifikacije: Model:iPad2 ekran:9.7touch LED display rezolucija:1024 interna memorija 16GB podrzani formati:mp4 , mov , mpeg4 avi , jpeg , tiff , gif , htm , html , pdf txt , xls , xlsx , aac , mp3 , wav HD 720p kamera , 5x digital zoom vga kamera , zvucnik , mikrofon -pin dock port , 3.5mm stereo jack Wi-fi , bluetooth , digital kompas punjiva baterija+punjac',50000.0E0,3)\n"+
				"INSERT INTO PRODUCT VALUES(19,'Elektro Scooter',2,'Elektro scooter sa novim akumulatorom. Dobro o\u00c4\u008duvan. Godina proizvodnje 2006.',10000.0E0,3)\n"+
				"INSERT INTO PRODUCT VALUES(20,'*PUMA* muska jakna za jesen decija',1,'Original puma jakna,deciji model velicina xxl.Duzina jakne je 70 cm,sirina 55cm[najsiri deo],rukavi dugi 60cm.Idealna za skolarce,unutra jajo prijatnog materijla,spolja sukavac[deblje platno.Ocuvana. Rajsflesus i nitne,oko rukava [zglobova] lastis.',1000.0E0,3)\n"+
				"INSERT INTO PRODUCT VALUES(21,'Sigurnosni sef',3,'Sigurnosni sef, dimenzije 50x42x35cm, otvaranje sa kljucem, set od vise kljuceva.',11800.0E0,3)\n"+
				"INSERT INTO OFFER VALUES(0,1,3,60500.0E0)\n"+
				"INSERT INTO OFFER VALUES(1,11,3,16300.0E0)\n"+
				"INSERT INTO OFFER VALUES(2,19,3,12000.0E0)\n"+
				"INSERT INTO OFFER VALUES(3,2,3,4550.0E0)\n"+
				"INSERT INTO OFFER VALUES(4,9,3,2200.0E0)\n"+
				"INSERT INTO OFFER VALUES(5,12,3,164.0E0)\n"+
				"INSERT INTO OFFER VALUES(6,4,3,460.0E0)\n"+
				"INSERT INTO OFFER VALUES(7,10,3,12.0E0)\n"+
				"INSERT INTO OFFER VALUES(8,3,3,1300.0E0)\n"+
				"INSERT INTO OFFER VALUES(9,6,3,450.0E0)\n"+
				"INSERT INTO OFFER VALUES(10,13,3,540.0E0)\n"+
				"INSERT INTO OFFER VALUES(11,16,3,2030.0E0)\n"+
				"INSERT INTO OFFER VALUES(12,7,0,80450.0E0)\n"+
				"INSERT INTO OFFER VALUES(13,11,0,16350.0E0)\n"+
				"INSERT INTO OFFER VALUES(14,14,0,226000.0E0)\n"+
				"INSERT INTO OFFER VALUES(15,19,0,14000.0E0)\n"+
				"INSERT INTO OFFER VALUES(16,9,0,2280.0E0)\n"+
				"INSERT INTO OFFER VALUES(17,18,0,54200.0E0)\n"+
				"INSERT INTO OFFER VALUES(18,10,0,16.0E0)\n"+
				"INSERT INTO OFFER VALUES(19,20,0,1400.0E0)\n"+
				"INSERT INTO OFFER VALUES(20,15,0,160.0E0)\n"+
				"INSERT INTO OFFER VALUES(21,5,2,10010.0E0)\n"+
				"INSERT INTO OFFER VALUES(22,9,2,2340.0E0)\n"+
				"INSERT INTO OFFER VALUES(23,18,2,54800.0E0)\n"+
				"INSERT INTO OFFER VALUES(24,4,2,500.0E0)\n"+
				"INSERT INTO OFFER VALUES(25,10,2,22.0E0)\n"+
				"INSERT INTO OFFER VALUES(26,3,2,1360.0E0)\n"+
				"INSERT INTO OFFER VALUES(27,17,2,600.0E0)\n"+
				"INSERT INTO OFFER VALUES(28,21,2,12900.0E0)\n"+
				"INSERT INTO OFFER VALUES(30,2,2,4750.0E0)\n"+
				"INSERT INTO OFFER VALUES(31,2,1,4896.0E0)\n"+
				"INSERT INTO OFFER VALUES(32,5,1,10300.0E0)\n"+
				"INSERT INTO OFFER VALUES(33,12,1,175.0E0)\n"+
				"INSERT INTO OFFER VALUES(34,18,1,55740.0E0)\n"+
				"INSERT INTO OFFER VALUES(35,4,1,540.0E0)\n"+
				"INSERT INTO OFFER VALUES(36,15,1,167.0E0)\n"+
				"INSERT INTO OFFER VALUES(37,20,1,1444.0E0)\n"+
				"INSERT INTO OFFER VALUES(38,1,1,60600.0E0)\n"+
				"INSERT INTO OFFER VALUES(39,14,1,234000.0E0)\n"+
				"INSERT INTO OFFER VALUES(40,19,1,14600.0E0)\n"+
				"INSERT INTO OFFER VALUES(41,3,1,1370.0E0)\n"+
				"INSERT INTO OFFER VALUES(42,17,1,650.0E0)\n"+
				"INSERT INTO OFFER VALUES(43,21,1,13100.0E0)\n"+
				"INSERT INTO OFFER VALUES(44,7,3,80700.0E0)\n"+
				"INSERT INTO OFFER VALUES(45,1,2,60750.0E0)\n"+
				"INSERT INTO OFFER VALUES(46,7,0,83000.0E0)").executeUpdate();
				//"session.createSQLQuery("INSERT INTO USER VALUES(1,'Vladimir','Djurovic','vladadju','nc86vl87','22222222222','vladadju@gmail.com',1)").executeUpdate();
				//session.createSQLQuery(" INSERT INTO USER VALUES(0,'Negovan','Djurovic','necadju','neca1706','0643140225','necadju@gmail.com',1)").executeUpdate();
				//session.createSQLQuery("INSERT INTO USER VALUES(2,'Borivoje','Djurovic','boradju','bora','354354353','boradju@gmail.com',1)");
				//session.createSQLQuery("INSERT INTO PRODUCT VALUES(1,'OPEL ASCONA 1.8i',2,'1985-to, 74 kw, atestiran TNG, u voznom stanju, pumpa za benzin ne radi, sajtne i rubovi su losi, gume kao nove, odjavljen...',60000.0E0,0)").executeUpdate();
				//session.createSQLQuery("").executeUpdate();
		
		System.out.println("aaaaaaaaaaaaaaaaaaaaaa");
		session.getTransaction().commit();
	}
	
	public static DBBroker getDbbroker(){
		if (dbbroker==null) dbbroker=new DBBroker();
		return dbbroker;
	}
	
	public boolean logIn(User user){
		session.beginTransaction();
		List<User> users = (List<User>) session.createQuery(
				"from User where userName like '"+ user.getUserName()+ "' and password like '"
						+ user.getPassword() + "'").list();
		session.getTransaction().commit();
		if(users.isEmpty())return false;
		return true;
		
		
		
	}
	
	public void addUser(User user){
		session.beginTransaction();
		session.saveOrUpdate(user);
		session.getTransaction().commit();
	}
	
	public User getUserByUserName(User user){
		session.beginTransaction();
		List<User> users = (List<User>) session.createQuery(
				"from User where userName like '"+ user.getUserName()+ "'").list();
		session.getTransaction().commit();
		if(users.isEmpty()) return null;
		return users.get(0);
	}
	
	public List<Product> getProducts(){
		
		session.beginTransaction();
		session.createQuery("from Product");
		List<Product> list=(List<Product>)session.createQuery("from Product").list();
		session.getTransaction().commit();
		return list;
	}
	
	public void addProduct(Product product){
		session.beginTransaction();
		session.saveOrUpdate(product);
		session.getTransaction().commit();
	}
	
	public void addOffer(Offer offer){
		session.beginTransaction();
		session.saveOrUpdate(offer);
		session.getTransaction().commit();
	}
	
	public double getHighestOffer(Product product){
		
		session.beginTransaction();
		List<Offer> offers = (List<Offer>) session.createQuery(
				"from Offer where productID like '"+ product.getProductID()+ "'").list();
		session.getTransaction().commit();
		if(offers.isEmpty()) return 0;
		else{
		int lastIndex=offers.size()-1;
		return offers.get(lastIndex).getPrice();
		}
	}
	
	public Product getProduct(int productID){
		session.beginTransaction();
		List<Product> products = (List<Product>) session.createQuery(
				"from Product where productID like '"+ productID+ "'").list();
		session.getTransaction().commit();
		if(products.isEmpty()) return null;
		else{
		
		return products.get(0);
		}
		
	}
	
	public List<Product> getProductsInCategory(int category){
		
		session.beginTransaction();
		
		List<Product> list=(List<Product>)session.createQuery("from Product where category like '"+ category+ "'").list();
		session.getTransaction().commit();
		return list;
	}
	
public List<Product> getMyProducts(int userID){
		
		session.beginTransaction();
		
		List<Product> list=(List<Product>)session.createQuery("from Product where userID like '"+ userID+ "'").list();
		session.getTransaction().commit();
		if(list.isEmpty()) return null;
		else{
		
		return list;
		}
	}

public void removeProduct(Product product){
	session.beginTransaction();
	session.delete(product);
	List<Offer> list=(List<Offer>)session.createQuery("from Offer where productID like '"+ product.getProductID()+ "'").list();
	for(int i=0;i<list.size();i++){
		session.delete(list.get(i));
	}
	session.getTransaction().commit();
}


public List<Offer> getAllOffers(Product product){
	
	session.beginTransaction();
	
	List<Offer> list=(List<Offer>)session.createQuery("from Offer where productID like '"+ product.getProductID()+ "'").list();
	session.getTransaction().commit();
	if(list.isEmpty()) return null;
	else{
	
	return list;
	}
}

public User getUserForOffer(Offer offer){
	session.beginTransaction();
	List<User> list=(List<User>)session.createQuery("from User where userID like '"+ offer.getUserID()+ "'").list();
	session.getTransaction().commit();
	if(list.isEmpty()) return null;
	else{
	
	return list.get(0);
	}
}
	
	
	

}
